package com.ctg.payroll2.service.xlsImport.imp;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.RandomStringUtils;
import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;

import com.ctg.payroll2.bean.po.ExcelInterfaceMaster;
import com.ctg.payroll2.bean.po.ExcelPublicMaster;
import com.ctg.payroll2.bean.po.PbEmployeeTmp;
import com.ctg.payroll2.bean.po.SysUser;
import com.ctg.payroll2.bean.po.exceloperator.PayInput;
import com.ctg.payroll2.bean.po.exceloperator.SocialResult;
import com.ctg.payroll2.dao.xlsImport.ExcelInterfaceDetailDAO;
import com.ctg.payroll2.dao.xlsImport.ExcelInterfaceMasterDAO;
import com.ctg.payroll2.dao.xlsImport.ExcelPublicMasterDAO;
import com.ctg.payroll2.dao.xlsImport.XlsPublicToosDAO;
import com.ctg.payroll2.dao.xlsImport.imp.IbatisXlsImportDAO;
import com.ctg.payroll2.util.Constants;
import com.ctg.payroll2.util.FormartVariable;
import com.ctg.payroll2.util.xlsImport.JxlUtils;
import com.ctg.platform.util.GenericsUtils;
import com.ctg.platform.util.Page;

/**
 * @author sami
 * 
 */
@SuppressWarnings("unchecked")
public class XlsImportServiceImpl<T> implements
		com.ctg.payroll2.service.xlsImport.XlsImportService<T> {
	private final static Logger logger = Logger
			.getLogger(XlsImportServiceImpl.class);

	private com.ctg.payroll2.dao.xlsImport.imp.IbatisXlsImportDAO importDAO;

	private JxlUtils ju;

	protected Class<T> entityClass;

	private com.ctg.payroll2.dao.xlsImport.ExcelInterfaceMasterDAO masterDAO;

	private com.ctg.payroll2.dao.xlsImport.ExcelInterfaceDetailDAO detailDAO;

	private com.ctg.payroll2.dao.xlsImport.ExcelPublicMasterDAO pmasterDAO;

	private XlsPublicToosDAO xlspublicToosDAO;

	private boolean isInsertTmp2Formal;

	public boolean isInsertTmp2Formal() {
		return isInsertTmp2Formal;
	}

	public void setInsertTmp2Formal(boolean isInsertTmp2Formal) {
		this.isInsertTmp2Formal = isInsertTmp2Formal;
	}

	public ExcelInterfaceMasterDAO getMasterDAO() {
		return masterDAO;
	}

	public void setMasterDAO(ExcelInterfaceMasterDAO masterDAO) {
		this.masterDAO = masterDAO;
	}

	public ExcelInterfaceDetailDAO getDetailDAO() {
		return detailDAO;
	}

	public void setDetailDAO(ExcelInterfaceDetailDAO detailDAO) {
		this.detailDAO = detailDAO;
	}

	public IbatisXlsImportDAO getImportDAO() {
		return importDAO;
	}

	public void setImportDAO(IbatisXlsImportDAO importDAO) {
		this.importDAO = importDAO;
	}

	public ExcelPublicMasterDAO getPmasterDAO() {
		return pmasterDAO;
	}

	public void setPmasterDAO(ExcelPublicMasterDAO pmasterDAO) {
		this.pmasterDAO = pmasterDAO;
	}

	public XlsImportServiceImpl() {
		entityClass = GenericsUtils.getSuperClassGenricType(getClass());
	}

	/*
	 * 
	 * @see com.ctg.payroll.service.XlsImportService#importData(java.lang.Object,
	 *      Object[]) 单个导入
	 */
	final public void importData(Object o, Object... object) {
		if (importDAO != null) {
			PbEmployeeTmp emp = null;
			// System.out.println(o.getClass().getSimpleName());
			if ("PbEmployeeTmp".equals(o.getClass().getSimpleName().trim())) {
				emp = (PbEmployeeTmp) o;
				HttpServletRequest request = ServletActionContext.getRequest();
				SysUser sysuer = (SysUser) request.getSession().getAttribute(
						Constants.CURRENT_USER);

				String companyCode = sysuer.getCompanyCode();
				if (null == companyCode)
					throw new RuntimeException("无效的公司编码");
				emp.setCompany_code(companyCode);
				importDAO.importData(emp);
			} else {
				importDAO.importData(o);
			}

		}
	}

	/**
	 * @param list
	 *            批量导入List 导入数据之前先进行上一批数据删除.deleteTempData(list.get(0));
	 */
	public void importDataList(List<T> list) {
		if (list != null && list.size() > 0) {
			deleteTempData(list.get(0));
		}
		if (list != null) {
			for (Object object : list) {
				importData(object);
			}
		} else {
			throw new NullPointerException();
		}
		if (this.isInsertTmp2Formal) {
			insertTmp2Formal(entityClass);
		}

	}

	/**
	 * 根据父类方法自动确认Bean
	 * 
	 * @param path
	 *            路径
	 * @param mapping
	 *            对应关系
	 * @throws Throwable
	 */
	public Map<String, List> importDataByJxlToBean(String path,
			HashMap<String, String> mapping) throws Throwable {
		List<T> goodlist = null;
		List<Map<String, String>> badlist = null;
		ju = new JxlUtils();
		ju.setStartIndexRow(1);
		ju.setPath(path);
		ju.setMapping(mapping);
		Map<String, List> listarry = ju.transform(entityClass);
		if (listarry.size() > 0) {
			goodlist = listarry.get(JxlUtils.GOODRECORES);
			importDataList(goodlist);
			badlist = listarry.get(JxlUtils.BADRECORES);
		}
		return listarry;
	}

	/**
	 * 如果无法根据泛型实例确认导入Bean,请手动设置class类型
	 * 
	 * @param path
	 *            路径
	 * @param clazz
	 *            实例Bean类型
	 * @param mapping
	 *            对应关系
	 * @throws Throwable
	 */
	public Map<String, List> importDataByJxlToBean(String path, Class clazz,
			HashMap<String, String> mapping) throws Throwable {
		entityClass = clazz;
		List<T> goodlist = null;
		List<Map<String, String>> badlist = null;
		ju = new JxlUtils();
		ju.setStartIndexRow(1);
		ju.setPath(path);
		ju.setMapping(mapping);
		Map<String, List> listarry = ju.transform(entityClass);
		// if (listarry.size() > 0) {
		// goodlist = listarry.get(JxlUtils.GOODRECORES);
		// importDataList(goodlist);
		// badlist = listarry.get(JxlUtils.BADRECORES);
		// }
		return listarry;
	}

	/**
	 * 从数据库读取导入配置信息
	 * 
	 * @param o
	 *            查询条件
	 * @return
	 */
	final protected List<Object> getMapping(Object o) {
		return importDAO.getMapping(o);
	}

	/*
	 * @see com.ctg.payroll.service.XlsImportService#getMappingToBean(java.lang.Object )
	 *      返回对应的mapping
	 */
	public HashMap<String, String> getMappingToBean(Object o) {
		HashMap<String, String> mapping = new HashMap<String, String>();
		List<HashMap<String, Object>> list = (List) getMapping(o);
		for (HashMap<String, Object> map : list) {
			mapping.put(map.get(Constants.IMPKEY).toString(), map.get(
					Constants.IMPVALUE).toString());
			// System.out.println("key:" + map.get(Constants.IMPKEY) + " value:
			// "
			// + map.get(Constants.IMPVALUE));
		}
		return mapping;
	}

	/*
	 * @see com.ctg.payroll.service.XlsImportService#pagedQuery(java.util.Map,
	 *      int, int, java.lang.Class) 查询导入类别
	 */
	public Page pagedQuery(Map parameterObject, int start, int limit,
			Class clazz) throws Throwable {
		if (clazz.newInstance() instanceof ExcelInterfaceMaster) {
			return masterDAO.pagedQuery(parameterObject, start, limit);
		}
		if (clazz.newInstance() instanceof ExcelPublicMaster) {
			return pmasterDAO.pagedQuery(parameterObject, start, limit);
		}
		return new Page();
	}

	/*
	 * @see com.ctg.payroll.service.XlsImportService#deleteTempData(java.lang.Object)
	 *      删除上批导入数据
	 */
	public void deleteTempData(Object o) {
		importDAO.deleteTempData(o);

	}

	/*
	 * @see com.ctg.payroll.service.XlsImportService#pagedQuery_temp(java.lang.Class,
	 *      java.util.Map, int, int, java.lang.Object[]) 查询导入失败记录
	 */
	public Page pagedQuery_temp(Map parameterObject, int start, int limit,
			Class... clazz) {
		if (clazz.length > 0) {
			for (Class cls : clazz) {
				Class<T> entityClass = cls;
				return importDAO.pagedQuery_temp(entityClass, parameterObject,
						start, limit);
			}
		} else {
			return importDAO.pagedQuery_temp(entityClass, parameterObject,
					start, limit);
		}
		return new Page();
	}

	/*
	 * 根据xls导入模板创建数据库表,并导入数据
	 * 
	 * @see com.ctg.payroll.service.XlsImportService#importDataByJxlToList(java.lang
	 *      .String, java.util.HashMap)
	 */
	public Map<String, List<Map<String, String>>> importDataByJxlToList(
			String path, HashMap<String, String> mapping) throws Throwable {
		boolean is_execute = false;
		Map<String, List<Map<String, String>>> map = null;
		List<Map<String, String>> badlist = null;
		String[] intiTable = intiTable(mapping);
		if (intiTable.length > 0) {
			is_execute = (Boolean) importDAO.executeSql(intiTable) ? true
					: false;
			if (is_execute) {
				ju = new JxlUtils();
				ju.setStartIndexRow(1);
				ju.setPath(path);
				ju.setMapping(mapping);
				map = ju.transform();
				List<Map<String, String>> list = map.get(JxlUtils.GOODRECORES);
				// badlist = map.get(JxlUtils.BADRECORES);
				// importDAO.executeSql(createSql(list));
				// update_imp_table();
			}
		} else {
			logger.info("Database Operation  Failure ");
		}
		return map;
	}

	/**
	 * 初始化数据库表
	 * 
	 * @param tableMap
	 * @return
	 */
	public String[] intiTable(HashMap<String, String> tableMap) {
		String tableName = RandomStringUtils.randomAlphabetic(5).toUpperCase();
		HttpServletRequest request = ServletActionContext.getRequest();
		request.getSession().setAttribute("tableName", tableName);
		StringBuffer createsqlinit = new StringBuffer();
		createsqlinit.append("create table " + tableName
				+ " (employee_id varchar(100),acompanycode varchar(100), ");
		for (Iterator<Entry<String, String>> it = tableMap.entrySet()
				.iterator(); it.hasNext();) {
			Entry<String, String> entry = it.next();
			createsqlinit.append("a" + entry.getValue());
			createsqlinit.append(" varchar2(100) ,");
		}
		String createsql = (createsqlinit.substring(0,
				createsqlinit.length() - 1))
				+ " )";
		// System.out.println(createsql);
		return new String[] { createsql };
	}

	/**
	 * 创建insert
	 * 
	 * @param list
	 * @return
	 */
	public List<String> createSql(List<Map<String, String>> list) {
		List<String> liststr = new ArrayList<String>();
		for (Map<String, String> mp : list) {
			StringBuffer sb = new StringBuffer();
			StringBuffer sb1 = new StringBuffer();
			HttpServletRequest request = ServletActionContext.getRequest();
			String tableName = (String) request.getSession().getAttribute(
					"tableName");
			SysUser sysuer = (SysUser) request.getSession().getAttribute(
					Constants.CURRENT_USER);
			String companyCode = sysuer.getCompanyCode();
			sb.append("insert into " + tableName + "  (acompanycode,");
			for (Iterator<Entry<String, String>> it = mp.entrySet().iterator(); it
					.hasNext();) {
				Entry<String, String> s = it.next();
				sb.append("a" + s.getKey());
				sb.append(",");
				sb1.append("'");
				sb1.append(s.getValue());
				sb1.append("',");
			}
			String insetsql = sb.toString().substring(0, sb.length() - 1)
					+ " )values ('" + companyCode + "',  "
					+ sb1.toString().substring(0, sb1.length() - 1) + ")";
			logger.info(insetsql);
			liststr.add(insetsql);
		}
		return liststr;
	}

	public List<HashMap<String, Object>> getDataforMap(Object parameterObject) {
		HttpServletRequest request = ServletActionContext.getRequest();
		String tableName = (String) request.getSession().getAttribute(
				"tableName");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("tableName", tableName);
		return importDAO.getDataforMap(map);
	}

	/**
	 * 提供社保和薪资的导入,从临时表到正式表的过程
	 * 
	 * @param parameterObject
	 * @param payclass_id
	 *            大类ID
	 * @return
	 */
	@SuppressWarnings("static-access")
	public HashMap<String, List> createsql2compensation(Object parameterObject,
			Object payclass_id, String type) {
		HttpServletRequest request = ServletActionContext.getRequest();
		SysUser sysuer = (SysUser) request.getSession().getAttribute(
				Constants.CURRENT_USER);
		Long user_id = sysuer.getEmployeeId() == null ? 0 : sysuer
				.getEmployeeId();
		HashMap<String, List> returnMap = new HashMap<String, List>();
		List<HashMap<String, Object>> list = getDataforMap(parameterObject);
		List<HashMap<String, Object>> badlist = new ArrayList<HashMap<String, Object>>();
		List<String> sqlList = new ArrayList<String>();
		int i = 0;
		if (list != null) {
			for (HashMap<String, Object> mp : list) {
				Pattern pattern = Pattern
						.compile("^(\\d{4})(0?[123456789]|1[012])$");
				Object company_code = mp.get("ACOMPANYCODE");
				Object period = mp.get("APERIOD") == null ? null : mp
						.get("APERIOD");
				Object employee_id = mp.get("EMPLOYEE_ID");
				boolean b = false;
				if (period != null && period.toString().length() == 6) {
					Matcher matcher = pattern.matcher(period.toString());
					b = matcher.matches();
				}

				if (company_code == null || employee_id == null || !b) {
					HashMap<String, Object> nmp = new HashMap<String, Object>();
					for (Iterator<Entry<String, Object>> it = mp.entrySet()
							.iterator(); it.hasNext();) {
						Entry<String, Object> en = it.next();
						nmp.put(en.getKey().replace("A", "").toLowerCase(), en
								.getValue());
					}
					badlist.add(nmp);
				} else {
					for (Iterator<Entry<String, Object>> it = mp.entrySet()
							.iterator(); it.hasNext();) {
						Entry<String, Object> entry = it.next();
						String newEntryKey = entry.getKey().replace("A", "");
						if (newEntryKey.equals("EMPLOYEECODE")
								|| newEntryKey.equals("COMPNYCODE")
								|| newEntryKey.equals("PERIOD")
								|| newEntryKey.equals("EMPLOYEE_ID")) {

						} else {
							HashMap<String, Object> paraMapforPayClass = new HashMap<String, Object>();
							paraMapforPayClass.put("employee_id", employee_id);
							paraMapforPayClass.put("period", period);
							paraMapforPayClass
									.put("company_code", company_code);
							// System.out.println(newEntryKey + ":----> :"
							// + entry.getValue());
							String sql = "";
							if ("social" == type) {
								try {
									paraMapforPayClass.put(
											"social_security_id", newEntryKey);
									if (importDAO
											.socialIsOnly(paraMapforPayClass)) {
										sql = "insert into cs_empsocial_result ( social_security_id, company_code,employee_id,period,result,data_come ) values('"
												+ newEntryKey
												+ "','"
												+ company_code
												+ "',"
												+ employee_id
												+ ",'"
												+ period
												+ "',"
												+ FormartVariable
														.toDouble(entry
																.getValue()
																+ "") + ",0)";
										sqlList.add(sql);
										logger.info("雇员:" + employee_id + " 年月"
												+ period + " 项目:"
												+ entry.getKey() + " 值:"
												+ entry.getValue());
									} else {
										HashMap<String, Object> nmp = new HashMap<String, Object>();
										for (Iterator<Entry<String, Object>> its = mp
												.entrySet().iterator(); its
												.hasNext();) {
											Entry<String, Object> en = its
													.next();
											nmp.put(en.getKey()
													.replace("A", "")
													.toLowerCase(), en
													.getValue());
										}
										if (badlist.contains(nmp)) {

										} else {
											badlist.add(nmp);
										}
									}
								} catch (Exception e) {
									e.printStackTrace();
								}
							}
							if ("payment" == type) {
								paraMapforPayClass.put("pay_classitem_id",
										newEntryKey);
								if (importDAO
										.payClassIsOnly(paraMapforPayClass)) {
									sql = "insert into cs_pay_input ( creator_code,modifier_code,pay_classitem_id,company_code,employee_id,pay_class_id,period,pay_money)"
											+ "values ('"
											+ user_id.intValue()
											+ "','"
											+ user_id.intValue()
											+ "', '"
											+ newEntryKey
											+ "','"
											+ company_code
											+ "','"
											+ employee_id
											+ "','"
											+ payclass_id
											+ "','"
											+ period
											+ "','"
											+ FormartVariable.toStr(entry
													.getValue()
													+ "") + "' )";
									sqlList.add(sql);
									logger.info("雇员:" + employee_id + " 年月"
											+ period + " 项目:" + newEntryKey
											+ " 值:" + entry.getValue());
								} else {
									HashMap<String, Object> nmp = new HashMap<String, Object>();
									for (Iterator<Entry<String, Object>> its = mp
											.entrySet().iterator(); its
											.hasNext();) {
										Entry<String, Object> en = its.next();
										nmp.put(en.getKey().replace("A", "")
												.toLowerCase(), en.getValue());
									}
									if (badlist.contains(nmp)) {

									} else {
										badlist.add(nmp);
									}

								}
							}
						}
					}
					i++;
				}
			}
			returnMap.put(Constants.BAD_LIST, badlist);
			returnMap.put(Constants.GOOD_LIST, sqlList);
			importDAO.executeSql(sqlList);// 执行insert语句
			logger.info("已执行完插入,临时表即将被删除");
			droptable();
			logger.info("所有过程已完成,临时已经删除");
		}
		return returnMap;
	}

	public void update_imp_table() {
		HashMap<String, Object> paramap = new HashMap<String, Object>();
		HttpServletRequest request = ServletActionContext.getRequest();
		String tableName = (String) request.getSession().getAttribute(
				"tableName");
		paramap.put("tableName", tableName);
		importDAO.update_imp_table(paramap);
	}

	/**
	 * @param pm
	 * @param o
	 *            带查询参数的从临时表到正式表的insert
	 */
	public void insertJTmp2Formal(Class o, HashMap<String, Object> paraMap) {
		importDAO.insertJTmp2Formal(o, paraMap);

	}

	/*
	 * @see com.ctg.payroll.service.XlsImportService#insertTmp2Formal(java.lang.Object
	 *      []) Object... o ,如果要手动指定导入类型请传入 参数o, 如不传,默认匹配泛型实例
	 */
	public void insertTmp2Formal(Class o) {
		importDAO.insertJTmp2Formal(o);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ctg.payroll2.service.xlsImport.XlsImportService#payClassIsOnly(java
	 *      .lang.Object) 薪资导入项是否唯一
	 */
	public Boolean payClassIsOnly(Object paraMap) {
		return importDAO.payClassIsOnly(paraMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ctg.payroll2.service.xlsImport.XlsImportService#executeSql(java.lang
	 *      .Object[]) 执行本地sql
	 */
	public Object executeSql(Object... obj) {
		return importDAO.executeSql(obj);
	}

	/*
	 * (non-Javadoc) 验证导入雇员是否唯一
	 * 
	 * @see com.ctg.payroll2.service.xlsImport.XlsImportService#isOnlyByempCsCode
	 *      (java.lang.Object[])
	 */
	public List<Map<String, Object>> isOnlyByempCsCode(Object... obj) {
		return importDAO.isOnlyByempCsCode();
	}

	public Boolean socialIsOnly(Object paraMap) {
		return importDAO.socialIsOnly(paraMap);
	}

	/**
	 * 雇员导入从临时表到正式表之前修改 政治面貌,部门,婚姻状况等下拉框形式的值为对应数据库的值
	 * 
	 * @return 修改记录数
	 */
	public Integer updateEmpcomboxMapping() {
		return importDAO.updateEmpcomboxMapping();
	}

	public int deletePayInputData(Object paraMap) {

		return xlspublicToosDAO.deletePayInputData(paraMap);
	}

	public int deleteSocialResult(Object paraMap) {
		return xlspublicToosDAO.deleteSocialResult(paraMap);
	}

	public Page queryPayInputData(Object paramap, Integer start, Integer limit) {
		return xlspublicToosDAO.queryPayInputData(paramap, start, limit);
	}

	public Integer queryPayInputDataCount(Object paramap) {
		return xlspublicToosDAO.queryPayInputDataCount(paramap);
	}

	public Page querySocialResult(Object paramap, Integer start, Integer limit) {
		return xlspublicToosDAO.querySocialResult(paramap, start, limit);
	}

	public Integer querySocialResultCount(Object paramap) {
		return xlspublicToosDAO.querySocialResultCount(paramap);
	}

	public XlsPublicToosDAO getXlspublicToosDAO() {
		return xlspublicToosDAO;
	}

	public void setXlspublicToosDAO(XlsPublicToosDAO xlspublicToosDAO) {
		this.xlspublicToosDAO = xlspublicToosDAO;
	}

	public List<HashMap<String, Object>> payClassIsValid(Object paramap) {
		return xlspublicToosDAO.payClassIsValid(paramap);
	}

	public List<HashMap<String, Object>> socialIsValid(Object paramap) {
		return xlspublicToosDAO.socialIsValid(paramap);
	}

	public void droptable() {
		HttpServletRequest request = ServletActionContext.getRequest();
		String tableName = (String) request.getSession().getAttribute(
				"tableName");
		java.sql.Connection con;
		try {
			con = importDAO.getCon();
			if (con != null) {
				try {
					con.prepareStatement("drop table " + tableName + "")
							.execute();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						con.close();
					} catch (SQLException e) {
						throw new RuntimeException("连接关闭失败,需要释放资源");
					}
				}
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}
}
